use XBase;
use Spreadsheet::WriteExcel;
use Encode;

$dbffile = "gf.dbf";


print "#Step 1. 读取股份对账数据dbf ...\n";
my $table = new XBase "$dbffile" or XBase->errstr;
my $cursor = $table->prepare_select("yyb", "khh", "sc","xwdm","tgzh","dzrq","zqdm","xtsl","zdsl",'bpyy');

my @datas = ();

while (my $data = $cursor->fetch_hashref) {
    
    if($data->{'zqdm'} !~ /131990/ and $data->{'tgzh'} !~ /^C9/ )
    {
        if($data->{'xwdm'} =~ /393387/ && $data->{'xtsl'} < 0)
    	{
    		push(@datas, {'lb'=>1,'yyb'=>$data->{'yyb'}, 'khh'=>$data->{'khh'}, 'sc'=>$data->{'sc'},'xwdm'=>$data->{'xwdm'},'tgzh'=>$data->{'tgzh'},'dzrq'=>$data->{'dzrq'} ,'zqdm'=>$data->{'zqdm'},'xtsl'=>$data->{'xtsl'},'zdsl'=>$data->{'zdsl'},'bpyy'=>$data->{'bpyy'}});
    	}
    	elsif($data->{'xwdm'} =~ /393387/)
    	{
    	    push(@datas, {'lb'=>3,'yyb'=>$data->{'yyb'}, 'khh'=>$data->{'khh'}, 'sc'=>$data->{'sc'},'xwdm'=>$data->{'xwdm'},'tgzh'=>$data->{'tgzh'},'dzrq'=>$data->{'dzrq'} ,'zqdm'=>$data->{'zqdm'},'xtsl'=>$data->{'xtsl'},'zdsl'=>$data->{'zdsl'},'bpyy'=>$data->{'bpyy'}});
    	}
    	else
    	{
    		push(@datas, {'lb'=>2,'yyb'=>$data->{'yyb'}, 'khh'=>$data->{'khh'}, 'sc'=>$data->{'sc'},'xwdm'=>$data->{'xwdm'},'tgzh'=>$data->{'tgzh'},'dzrq'=>$data->{'dzrq'} ,'zqdm'=>$data->{'zqdm'},'xtsl'=>$data->{'xtsl'},'zdsl'=>$data->{'zdsl'},'bpyy'=>$data->{'bpyy'}});
	   	}
    }
   
    elsif($data->{'tgzh'} =~ /^C9/)
    {
    	push(@datas, {'lb'=>4,'yyb'=>$data->{'yyb'}, 'khh'=>$data->{'khh'}, 'sc'=>$data->{'sc'},'xwdm'=>$data->{'xwdm'},'tgzh'=>$data->{'tgzh'},'dzrq'=>$data->{'dzrq'} ,'zqdm'=>$data->{'zqdm'},'xtsl'=>$data->{'xtsl'},'zdsl'=>$data->{'zdsl'},'bpyy'=>$data->{'bpyy'}});
    }
    elsif($data->{'zqdm'} =~ /131990/)
    {
    	push(@datas, {'lb'=>5,'yyb'=>$data->{'yyb'}, 'khh'=>$data->{'khh'}, 'sc'=>$data->{'sc'},'xwdm'=>$data->{'xwdm'},'tgzh'=>$data->{'tgzh'},'dzrq'=>$data->{'dzrq'} ,'zqdm'=>$data->{'zqdm'},'xtsl'=>$data->{'xtsl'},'zdsl'=>$data->{'zdsl'},'bpyy'=>$data->{'bpyy'}});
    }
    else
    {
    	push(@datas, {'lb'=>6,'yyb'=>$data->{'yyb'}, 'khh'=>$data->{'khh'}, 'sc'=>$data->{'sc'},'xwdm'=>$data->{'xwdm'},'tgzh'=>$data->{'tgzh'},'dzrq'=>$data->{'dzrq'} ,'zqdm'=>$data->{'zqdm'},'xtsl'=>$data->{'xtsl'},'zdsl'=>$data->{'zdsl'},'bpyy'=>$data->{'bpyy'}});
    }
}

$table->close();
sub paixu
{
	$a->{'lb'} <=> $b->{'lb'} or $a->{'sc'} <=> $b->{'sc'} or $a->{'zqdm'} <=> $b->{'zqdm'} or $a->{'khh'} <=> $b->{'khh'} or $a->{'tgzh'} <=> $b->{'tgzh'} or $a->{'xtsl'} <=> $b->{'xtsl'} or $a->{'xwdm'} <=> $b->{'xwdm'} 
}

print "#Step 2. 对数据进行分类处理...\n";
@newdatas = sort paixu @datas;


print "#Step 3. 写入新增股份对账不平文件...\n";

my $dest_book  = Spreadsheet::WriteExcel->new("新增股份对账不平.xls")  or die "Could not create a new Excel file in $destname: $!";

my $dest_sheet = $dest_book->addworksheet(decode("gbk",'股份对账不平'));


$khhfmt = $dest_book->add_format(num_format => '0000000000', border=>1);
$saxwdmfmt = $dest_book->add_format(num_format => '000000', border=>1);
$shxwdmfmt = $dest_book->add_format(num_format => '00000', border=>1);
$tgzhfmt = $dest_book->add_format(num_format => '0000000000', align=>'left', border=>1);
$zqdmmfmt = $dest_book->add_format(num_format => '000000', border=>1);
$comfmt =  $dest_book->add_format(border   => 1);
$headerfmt = $dest_book->add_format(border   => 1, bold=>1);

my $i = 0;

	$dest_sheet->write($i, 0, decode("gbk","类别"), $headerfmt);
	$dest_sheet->write($i, 1, decode("gbk","营业部"), $headerfmt);
	$dest_sheet->write($i, 2, decode("gbk","客户号"), $headerfmt);
	$dest_sheet->write($i, 3, decode("gbk","市场"), $headerfmt);
	$dest_sheet->write($i, 4, decode("gbk","席位代码"), $headerfmt);
	$dest_sheet->write($i, 5, decode("gbk","托管账号"), $headerfmt);
	$dest_sheet->write($i, 6, decode("gbk","对账日期"), $headerfmt);
	$dest_sheet->write($i, 7, decode("gbk","证券代码"), $headerfmt);
	$dest_sheet->write($i, 8, decode("gbk","系统数量"), $headerfmt);
	$dest_sheet->write($i, 9, decode("gbk","中登数量"), $headerfmt);
	$dest_sheet->write($i, 10, decode("gbk","不平原因"), $headerfmt);
$i++;

foreach $data(@newdatas)
{
	$dest_sheet->write($i, 0, $data->{'lb'}, $comfmt);
	$dest_sheet->write($i, 1, decode("gbk",$data->{'yyb'}), $comfmt);
	$khh = sprintf("%010s",$data->{'khh'});
	
	$dest_sheet->write_string($i, 2, $khh, $khhfmt);
	$dest_sheet->write($i, 3, decode("gbk",$data->{'sc'}), $comfmt);
	if($data->{'tgzh'} =~ /^[a-z]+/i)
	{
		$dest_sheet->write_string($i, 4, $data->{'xwdm'}, $shxwdmfmt);
	}
	else
	{
		$dest_sheet->write_string($i, 4, $data->{'xwdm'}, $saxwdmfmt);
	}
	$dest_sheet->write_string($i, 5, $data->{'tgzh'}, $tgzhfmt);
	$dest_sheet->write_string($i, 6, $data->{'dzrq'}, $comfmt);
	$dest_sheet->write_string($i, 7, $data->{'zqdm'}, $zqdmmfmt);
	$dest_sheet->write($i, 8, $data->{'xtsl'}, $comfmt);
	$dest_sheet->write($i, 9, $data->{'zdsl'}, $comfmt);
	$dest_sheet->write($i, 10, '', $comfmt);
	
	$i++;
}

$dest_book->close();

print "#Step 4. 处理结束！！！！！！！！！！！\n";

